package com.travel.dao.impl;

/**
 * 评论功能
 * @author zourenhe
 *
 */

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.travel.dao.HotelCommentDao;
import com.travel.entity.Comment;
import com.travel.entity.Hotel;
import com.travel.entity.ScenicComment;
import com.travel.entity.User;
import com.travel.util.DBUtils;
import com.travel.util.PageInfo;

public class HotelCommentDaoImpl implements HotelCommentDao {

	private DBUtils db = new DBUtils();

	@Override
	public List<Comment> find() {
		List<Comment>  list= new ArrayList<Comment>();
		StringBuffer sb=new StringBuffer();
		sb.append("select")
		.append(" C.comment_content,")
		.append(" C.comment_datetime,")
		.append(" C.user_id,")
		.append(" C.hotel_id,")
		.append(" C.parent_comment_id,")
		.append(" H.hotel_picture,")
		.append(" U.user_name,")
		.append(" H.hotel_name,")
		.append(" U.user_picture")
		.append(" from tab_hotel_comment C")
		.append(" inner join tab_user U")
		.append(" on C.user_id=U.user_id")
		.append(" inner join tab_hotel H")
		.append(" on C.hotel_id=H.hotel_id");
		ResultSet rs=db.query(sb.toString());
		try {
			while (rs.next()) {
				String commentContent = rs.getString("comment_content");
				String commentDatetime = rs.getString("comment_datetime");
				String hotelPicture = rs.getString("hotel_picture");
				Integer userId = rs.getInt("user_id");
				Integer hotelId = rs.getInt("hotel_id");
				Integer parentCommentId = rs.getInt("parent_comment_id");
				String userName = rs.getString("user_name");
				String hotelName = rs.getString("hotel_name");
				String userPicture = rs.getString("user_picture");
				
				Comment  comment = new  Comment(null,commentContent,commentDatetime,userId,hotelId,parentCommentId,new User(null,userName,null,null,null,null,null,userPicture),new Hotel(null,hotelName,null,hotelPicture,null,null,null));
				
				list.add(comment);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		
		return list;
	}

	@Override
	public int deleteById(int commentID) {
		return db.update("delete from tab_hotel_comment where comment_id = ?", commentID);

	}

	@Override
	public int insert(Comment comment) {
		String sql = "insert into tab_hotel_comment(comment_id,comment_content,comment_datetime,user_id,hotel_id,parent_comment_id) values(?,?,?,?,?,?)";
		int i = db.update(sql, comment.getCommentId(), comment.getCommentContent(), comment.getCommentDatetime(),
				comment.getUserId(), comment.getHotelId(), comment.getParentCommentId());

		return i;

	}

	@Override
	public PageInfo<Comment> findByPage(int pageNum, int pageSize) {
		// 1.获取分页结果的数据
		List<Comment> data = new ArrayList<Comment>();
		
		StringBuffer sb = new StringBuffer();
		sb.append("select")
			.append(" C.comment_id,")
			.append(" C.comment_content,")
			.append(" C.comment_datetime,")
			.append(" C.user_id,")
			.append(" C.hotel_id,")
			.append(" C.parent_comment_id,")
			.append(" U.user_name,")
			.append(" H.hotel_name")
			.append(" from tab_hotel_comment C")
			.append(" inner join tab_user U")
			.append("  on C.user_id=U.user_id")
			.append(" inner join tab_hotel H")
			.append("  on C.hotel_id=H.hotel_id")
			.append(" limit ?,?");
		
		ResultSet rs  = db.query(sb.toString() ,(pageNum-1)*pageSize, pageSize);
		// 遍历结果集
		try {
			while (rs.next()) {
				Integer commentId  = rs.getInt("comment_id");
				String  commentContent = rs.getString("comment_content");
				String  commentDatetime = rs.getString("comment_datetime");
				Integer userId = rs.getInt("user_id");
				Integer hotelId=rs.getInt("hotel_id");
				Integer parentCommentId = rs.getInt("parent_comment_id");
				String  userName = rs.getString("user_name");
				String  hotelName = rs.getString("hotel_name");
				
				Comment  comment = new  Comment(commentId,commentContent,commentDatetime,userId,hotelId,parentCommentId,new User(null,userName,null,null,null,null,null,null),new Hotel(null,hotelName,null,null,null,null,null));
		
				// 添加到集合中
				data.add(comment);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		// 2.获取总记录数(未分页之前)
		int total = 0;
		sb = new StringBuffer();
		sb.append("select")
		  .append(" count(*)")
		  .append(" from tab_hotel_comment");
		
		rs  = db.query(sb.toString());
		// 遍历结果集
		try {
			while (rs.next()) {
				total  = rs.getInt(1);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		// 构建分页对象
		PageInfo<Comment> pageInfo = new PageInfo<Comment>(data, pageNum, pageSize, total);
		
		return pageInfo;
	}

}
